﻿Imports System.Data
Imports System.Data.SqlClient

Public Class Maestros_ADO
    ReadOnly oConnections As New ConexionADO()
    ReadOnly oCnx As New SqlConnection(oConnections.GetCnx)
    ReadOnly oCnxCtb As New SqlConnection(ConexionADO.GetCnxContabilidad)
    ReadOnly cmd As New SqlCommand()

    Public Function Get_Aerolinea() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT CodEmpresa, NomEmpresa, CodAnalisis FROM dbo.M_EmpresaTransporte"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Aerolinea " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_Analisis(ByVal sFiltro As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT Codanl, Desanl, Codanl+' '+Desanl AS Descripcion1 FROM dbo.Analisis WHERE (len(codanl)=6) " & sFiltro

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Análisis " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_Comunidades() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codComunidad, desComunidad FROM dbo.M_Comunidad WHERE (codComunidad<>'0000000')"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Comunidad " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_Contratante() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codContrataViaje, desContrataViaje, CodAnalisis FROM dbo.M_Contratante"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Contratante " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_Cuentas(ByVal sFiltro As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT Codcta, Descta, Codcta+' '+Descta AS Descripcion1 FROM dbo.Plan_cuentas WHERE (len(codcta)=7) " & sFiltro

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Cuentas " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_Diario() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnxCtb
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codasn,desasn FROM dbo.Diarios"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Diario " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
        Return dt
    End Function

    Public Function Get_Documentos() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codTipoDocumento, desTipoDocumento FROM dbo.M_Tipo_Documento"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Tipo documento" & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_EstadoReserva() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codEstadoReserva, desEstadoReserva FROM dbo.m_estado_reserva"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Estado Reserva" & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_EstadoVuelo() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codEstadoVuelo, desEstadoVuelo FROM dbo.m_estado_vuelo"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Estado Vuelo" & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_LugarResidencia() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT CodLugar, NomLugar FROM dbo.M_Lugar WHERE TipLugar='R'"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Lugar residencia " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_LugarVuelo() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT CodLugar, NomLugar FROM dbo.M_Lugar WHERE TipLugar='L'"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Lugar vuelo " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_Monedas() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codMoneda, nomMoneda FROM dbo.M_Moneda"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Moneda " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_MotivoViaje() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT codMotivo, desMotivo FROM dbo.M_MotivoViaje"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("MotivoViaje " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_Periodo() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnxCtb
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT anomes,desmes FROM dbo.Meses"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Periodo " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
        Return dt
    End Function

    Public Function Get_RutaVuelo(ByVal sOrigen As String) As DataTable
        Dim scond As String
        If String.Empty = sOrigen.Trim Then
            scond = "WHERE (r.flginactivo = 0)"
        Else
            scond = String.Format("WHERE (r.flginactivo = 0) AND (r.CodOrigen = '{0}' )", sOrigen)
        End If
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text

            cmd.CommandText = "SELECT CodOrigen+CodDestino as codigo,lo.NomLugar+'/'+ld.NomLugar as descripcion from dbo.m_rutas r " & _
                              "INNER JOIN dbo.m_lugar lo on r.codorigen=lo.codlugar INNER JOIN dbo.m_lugar ld on r.coddestino=ld.codlugar " & scond
            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Lugar residencia " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_TipoCambio(ByVal sFecha As String) As Decimal
        Dim nmonven As Decimal = 0
        Dim sql As String = String.Format("select moncom,monven from dbo.tipo_cambio where fchmov='{0}'", sFecha)
        Using cn As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Try
                cn.Open()
                Using cmd As New SqlCommand(sql, cn)
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable("tc")
                        da.Fill(dt)
                        Dim drCurrent As DataRow
                        For Each drCurrent In dt.Rows
                            nmonven = drCurrent("monven")
                        Next
                    End Using
                End Using
                cn.Close()
                Return nmonven
            Catch ex As Exception
                cn.Close()
                MsgBox("Maestros_ADO (Get_TipoCambio) --> " & ex.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function


    Public Function Get_TipoPasajero() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT CodTipoPasajero, NomTipoPasajero FROM dbo.M_TipoPasajero WHERE flgInactivo=0"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Contratante " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_TipoValija() As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = "SELECT TipoValija, desTipoValija FROM dbo.M_Tipo_Valija"

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("Tipo valija " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function Get_TipoVuelo(ByVal sCodEmp As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = oCnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT CodTipo, NomTipo FROM dbo.M_TipoVuelo WHERE (CodEmpresa='{0}')", sCodEmp)

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch myException As Exception
            MsgBox("Tipo Vuelo --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function
End Class